import sys
import os
import pandas as pd
import numpy as np
from PyQt5.QtWidgets import (QApplication, QMainWindow, QPushButton, QVBoxLayout,
                             QHBoxLayout, QFileDialog, QLabel, QWidget, QLineEdit,
                             QTextEdit, QMessageBox, QProgressBar)
from PyQt5.QtCore import Qt, QThread, pyqtSignal
from datetime import datetime


class ProcessingThread(QThread):
    """处理文件的线程，避免界面卡顿"""
    progress_updated = pyqtSignal(int)
    message_updated = pyqtSignal(str)
    processing_finished = pyqtSignal(bool)
    stats_updated = pyqtSignal(dict)

    def __init__(self, file_path, replace_path, chunk_size=10000):
        super().__init__()
        self.file_path = file_path
        self.replace_path = replace_path
        self.chunk_size = chunk_size
        self.stats = {
            'total_chunks': 0,
            'processed_chunks': 0,
            'total_rows': 0,
            'call_rows': 0,
            'valid_file_ids': 0,
            'invalid_file_ids': 0,
            'deleted_files': 0,
            'failed_deletions': 0,
            'skipped_rows': 0
        }
        # 内存日志缓存（列表形式）
        self.log_cache = []
        # 日志文件路径（CSV格式）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        self.log_file_path = f"delete_log_{timestamp}.csv"
        # 日志表头
        self.log_cache.append(["时间戳", "FileID", "原始路径", "替换后路径", "处理结果", "备注"])

    def append_to_log(self, file_id, old_path, new_path, result, remark=""):
        """在内存中缓存日志记录（不立即写入磁盘）"""
        try:
            # 直接添加到内存列表
            self.log_cache.append([
                self.get_timestamp(),
                file_id,
                old_path,
                new_path,
                result,
                remark
            ])
        except Exception as e:
            self.log_message(f"日志缓存失败: {str(e)}")

    def export_log_to_csv(self):
        """处理完成后将内存中的日志一次性导出为CSV"""
        try:
            # 用pandas快速写入CSV
            df = pd.DataFrame(self.log_cache[1:], columns=self.log_cache[0])
            # 使用utf-8-sig编码确保中文正常显示
            df.to_csv(self.log_file_path, index=False, encoding='utf-8-sig')
            self.log_message(f"日志已导出至: {self.log_file_path}")
        except Exception as e:
            self.log_message(f"日志导出失败: {str(e)}")

    def get_timestamp(self):
        """获取当前时间戳（精确到毫秒）"""
        return datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")[:-3]

    def log_message(self, message):
        """添加时间戳并发送日志消息"""
        timestamped_message = f"[{self.get_timestamp()}] {message}"
        self.message_updated.emit(timestamped_message)

    def is_valid_file_id(self, file_id):
        """
        校验FileID的有效性（针对int64类型）：
        1. 必须是整数类型（int或numpy int64）
        2. 转换为字符串后长度小于10
        3. 必须是纯数字（整数本身已保证）
        4. 不能包含特殊符号和空格（整数本身已保证）
        """
        # 检查是否为整数类型（包括numpy的int64）
        if not isinstance(file_id, (int, np.integer)):
            return False

        # 转换为字符串进行长度检查
        file_id_str = str(file_id)

        # 检查长度是否小于10
        if len(file_id_str) >= 10:
            return False

        # 整数类型本身已保证是纯数字，且不包含特殊符号和空格
        return True

    def run(self):
        try:
            # 检查是否存在必要列
            self.log_message("正在检查文件结构...")
            with pd.ExcelFile(self.file_path) as xls:
                sample = xls.parse('Sheet1', nrows=1)
                if 'FileID' not in sample.columns:
                    self.log_message("错误：文件中未找到'FileID'列!")
                    self.processing_finished.emit(False)
                    return

            # 获取文件总行数
            self.log_message("正在统计文件行数...")
            with pd.ExcelFile(self.file_path) as xls:
                df = xls.parse('Sheet1')
                rows, columns = df.shape

            # 计算总分块数
            self.stats['total_chunks'] = (rows // self.chunk_size) + (1 if rows % self.chunk_size > 0 else 0)
            self.log_message(f"文件总行数: {rows}, 分块大小: {self.chunk_size}, 总分块数: {self.stats['total_chunks']}")

            # 分块读取和处理Excel文件
            processed_chunks = 0
            total_processed = 0

            with pd.ExcelFile(self.file_path) as xls:
                for i in range(self.stats['total_chunks']):
                    start_row = i * self.chunk_size
                    end_row = min((i + 1) * self.chunk_size, rows)

                    # 读取当前块的数据
                    chunk = xls.parse('Sheet1', nrows=end_row - start_row, skiprows=range(1, start_row))
                    self.stats['total_rows'] += len(chunk)

                    # 筛选ModuleID列值为'Call'的行
                    call_rows = chunk[chunk['ModuleID'] == 'Call']
                    self.stats['call_rows'] += len(call_rows)

                    # 处理当前块中符合条件的行
                    for _, row in call_rows.iterrows():
                        file_id = row.get('FileID', '')

                        # 校验FileID
                        if not self.is_valid_file_id(file_id):
                            self.stats['invalid_file_ids'] += 1
                            self.append_to_log(
                                file_id,
                                row.get('FilePath', ''),
                                '',
                                '跳过',
                                '无效的FileID（长度≥10或非字符串）'
                            )
                            continue

                        self.stats['valid_file_ids'] += 1
                        old_path = row['FilePath']

                        if isinstance(old_path, str) and old_path.startswith('../Resource'):
                            # 替换路径
                            new_path = old_path.replace('../Resource', self.replace_path)

                            # 删除文件
                            if os.path.exists(new_path):
                                try:
                                    os.remove(new_path)
                                    self.stats['deleted_files'] += 1
                                    self.append_to_log(file_id, old_path, new_path, '成功')
                                except Exception as e:
                                    self.stats['failed_deletions'] += 1
                                    self.append_to_log(file_id, old_path, new_path, '失败', str(e))
                            else:
                                self.stats['skipped_rows'] += 1
                                self.append_to_log(file_id, old_path, new_path, '跳过', '文件不存在')
                        else:
                            self.stats['skipped_rows'] += 1
                            self.append_to_log(file_id, old_path, '', '跳过', '路径不以../Resource开头')

                    # 更新进度
                    processed_chunks += 1
                    self.stats['processed_chunks'] = processed_chunks
                    total_processed += len(chunk)
                    overall_progress = int((total_processed / rows) * 100)
                    self.progress_updated.emit(overall_progress)

                    # 每处理5个块更新一次统计信息
                    if processed_chunks % 5 == 0:
                        self.stats_updated.emit(self.stats)

            # 处理完成后导出日志
            self.export_log_to_csv()

            # 发送最终统计信息
            self.stats_updated.emit(self.stats)
            self.log_message("处理完成!")

            # 输出处理摘要
            summary = (
                f"\n处理摘要:\n"
                f"总块数: {self.stats['processed_chunks']}/{self.stats['total_chunks']}\n"
                f"总行数: {self.stats['total_rows']}\n"
                f"ModuleID为'Call'的行数: {self.stats['call_rows']}\n"
                f"有效的FileID: {self.stats['valid_file_ids']}\n"
                f"无效的FileID: {self.stats['invalid_file_ids']}\n"
                f"成功删除的文件数: {self.stats['deleted_files']}\n"
                f"删除失败的文件数: {self.stats['failed_deletions']}\n"
                f"跳过的行数: {self.stats['skipped_rows']}"
            )
            self.log_message(summary)

            self.processing_finished.emit(True)
        except Exception as e:
            self.log_message(f"发生错误: {str(e)}")
            # 即使发生错误也尝试导出已缓存的日志
            self.export_log_to_csv()
            self.processing_finished.emit(False)


class ExcelPathProcessor(QMainWindow):
    """Excel文件路径处理工具的主窗口"""

    def __init__(self):
        super().__init__()
        self.init_ui()
        self.current_file = None

    def init_ui(self):
        """初始化用户界面"""
        self.setWindowTitle('Excel文件路径处理工具')
        self.setGeometry(100, 100, 800, 600)

        # 创建中央部件和布局
        central_widget = QWidget()
        self.setCentralWidget(central_widget)
        main_layout = QVBoxLayout(central_widget)

        # 文件选择区域
        file_layout = QHBoxLayout()
        self.file_label = QLabel('未选择文件')
        self.file_button = QPushButton('选择文件')
        self.file_button.clicked.connect(self.select_file)

        file_layout.addWidget(self.file_label)
        file_layout.addWidget(self.file_button)
        main_layout.addLayout(file_layout)

        # 替换路径输入区域
        replace_layout = QHBoxLayout()
        replace_label = QLabel('替换路径 (replacePath):')
        self.replace_input = QLineEdit()
        self.replace_input.setPlaceholderText('输入替换路径...')

        replace_layout.addWidget(replace_label)
        replace_layout.addWidget(self.replace_input)
        main_layout.addLayout(replace_layout)

        # 分块大小设置
        chunk_layout = QHBoxLayout()
        chunk_label = QLabel('分块大小 (行数):')
        self.chunk_input = QLineEdit('10000')
        self.chunk_input.setToolTip('较大的值会增加内存使用，但减少读取次数\n较小的值会降低内存使用，但增加读取次数')

        chunk_layout.addWidget(chunk_label)
        chunk_layout.addWidget(self.chunk_input)
        main_layout.addLayout(chunk_layout)

        # 处理按钮
        self.process_button = QPushButton('开始处理')
        self.process_button.clicked.connect(self.process_file)
        self.process_button.setEnabled(False)
        main_layout.addWidget(self.process_button)

        # 进度条
        self.progress_bar = QProgressBar()
        self.progress_bar.setValue(0)
        main_layout.addWidget(self.progress_bar)

        # 统计信息显示
        stats_label = QLabel('处理统计:')
        main_layout.addWidget(stats_label)

        self.stats_text = QTextEdit()
        self.stats_text.setReadOnly(True)
        self.stats_text.setMaximumHeight(120)
        main_layout.addWidget(self.stats_text)

        # 日志显示区域
        log_label = QLabel('处理日志:')
        main_layout.addWidget(log_label)

        self.log_text = QTextEdit()
        self.log_text.setReadOnly(True)
        main_layout.addWidget(self.log_text)

        # 状态栏
        self.statusBar().showMessage('就绪')

    def select_file(self):
        """选择Excel文件"""
        file_path, _ = QFileDialog.getOpenFileName(
            self, '选择Excel文件', '', 'Excel Files (*.xlsx)'
        )

        if file_path:
            self.current_file = file_path
            self.file_label.setText(os.path.basename(file_path))
            self.process_button.setEnabled(True)
            self.log_text.clear()
            self.stats_text.clear()
            self.log_text.append(f"已选择文件: {file_path}")
            self.statusBar().showMessage(f"已选择文件: {os.path.basename(file_path)}")

    def process_file(self):
        """处理文件"""
        if not self.current_file:
            QMessageBox.warning(self, '警告', '请先选择一个Excel文件!')
            return

        replace_path = self.replace_input.text().strip()
        if not replace_path:
            QMessageBox.warning(self, '警告', '请输入替换路径!')
            return

        # 获取分块大小
        try:
            chunk_size = int(self.chunk_input.text())
            if chunk_size <= 0:
                raise ValueError("分块大小必须是正整数")
        except ValueError as e:
            QMessageBox.warning(self, '警告', f'无效的分块大小: {str(e)}')
            return

        # 确认对话框
        reply = QMessageBox.question(
            self, '确认操作',
            f'确定要处理文件 "{os.path.basename(self.current_file)}" 吗?\n'
            f'所有ModuleID为"Call"且FilePath以 "../Resource" 开头的文件路径将被替换为 "{replace_path}" 并删除对应文件。\n'
            f'文件将以每块 {chunk_size} 行的方式处理。\n'
            '此操作不可撤销!',
            QMessageBox.Yes | QMessageBox.No, QMessageBox.No
        )

        if reply == QMessageBox.Yes:
            # 禁用按钮防止重复点击
            self.process_button.setEnabled(False)
            self.file_button.setEnabled(False)

            # 清空日志并显示开始信息
            self.log_text.clear()
            self.stats_text.clear()
            self.log_text.append(f"开始处理文件: {os.path.basename(self.current_file)}")
            self.log_text.append(f"替换路径: {replace_path}")
            self.log_text.append(f"分块大小: {chunk_size} 行")

            # 创建并启动处理线程
            self.processing_thread = ProcessingThread(self.current_file, replace_path, chunk_size)
            self.processing_thread.progress_updated.connect(self.update_progress)
            self.processing_thread.message_updated.connect(self.update_log)
            self.processing_thread.processing_finished.connect(self.processing_complete)
            self.processing_thread.stats_updated.connect(self.update_stats)
            self.processing_thread.start()

    def update_progress(self, value):
        """更新进度条"""
        self.progress_bar.setValue(value)

    def update_log(self, message):
        """更新日志"""
        self.log_text.append(message)

    def update_stats(self, stats):
        """更新统计信息"""
        stats_text = (
            f"已处理块数: {stats['processed_chunks']}/{stats['total_chunks']}\n"
            f"总行数: {stats['total_rows']}\n"
            f"Call行数: {stats['call_rows']}\n"
            f"有效的FileID: {stats['valid_file_ids']}\n"
            f"无效的FileID: {stats['invalid_file_ids']}\n"
            f"已删除文件: {stats['deleted_files']}\n"
            f"删除失败: {stats['failed_deletions']}\n"
            f"跳过行数: {stats['skipped_rows']}"
        )
        self.stats_text.setText(stats_text)

    def processing_complete(self, success):
        """处理完成后的回调"""
        if success:
            self.statusBar().showMessage('处理完成')
            QMessageBox.information(self, '成功', '文件处理完成!')
        else:
            self.statusBar().showMessage('处理失败')
            QMessageBox.critical(self, '错误', '文件处理过程中发生错误!')

        # 重新启用按钮
        self.process_button.setEnabled(True)
        self.file_button.setEnabled(True)


if __name__ == '__main__':
    # 确保中文显示正常
    os.environ['QT_FONT_DPI'] = '96'

    app = QApplication(sys.argv)
    window = ExcelPathProcessor()
    window.show()
    sys.exit(app.exec_())
